package starkuz.lg.dao.impl.sites;

import starkuz.lg.dao.ifaces.sites.LeadSiteDao;
import starkuz.lg.model.SiteStatuses;
import starkuz.lg.model.sites.LeadSite;
import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.sql.Date;
import java.util.*;

@Repository
public class LeadSiteDaoImpl implements LeadSiteDao {

    private static final String SELECT_ALL_LEAD_SITES =
            "select * from lead_sites";
    private static final String SELECT_ALL_ACTUAL_LEAD_SITES_BY_USER_ID =
            "select * from lead_sites where ownerId = ? and not status = 'deleted'";
    private static final String SELECT_ALL_LEAD_SITES_BY_USER_ID =
            "select * from lead_sites where ownerId = ?";
    private static final String SELECT_LEAD_SITE_BY_ID = "select * from lead_sites where id = ?";
    private static final String SELECT_ALL_LEAD_SITES_BY_USER_ID_AND_STATUS =
            "select * from lead_sites where ownerId = ? and status = ?";
    private static final String SELECT_ALL_LEAD_SITES_BY_STATUS =
            "select * from lead_sites where status = ?";
    private static final String SELECT_ALL_LEAD_SITES_BY_USER_ID_AND_STATUS_AND_DATE_BETWEEN =
            "select * from lead_sites where ownerId = ? and status = ? and dateMade between ? and ?";

    private static final String DELETE_LEAD_SITE_BY_ID = "delete from lead_sites where id = ?";
    private static final String UPDATE_LEAD_SITE_TO_DELETED =
            "update lead_sites set status = 'deleted' where id = ?";
    private static final String INSERT_LEAD_SITE =
            "insert into lead_sites (ownerId, idTemplate, titleTag, keywordTag, descripTag, mainHeader, briefDef," +
                    " inputsCode, companyName, mySite, dopText, action, formCode, statCode, marketType," +
                    " status,bgImg, blackBox, dateMade, dateLastChange)" +
                    " values (?, ?, ?, ?, ?, ? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,?)";
    private static final String SET_LEAD_SITE_STATUS_BY_ID = "update lead_sites set status = ? where id = ?";
    private static final String UPDATE_LEAD_SITE =
            "update lead_sites set ownerId = ?, idTemplate=?, titleTag = ?, keywordTag = ?, descripTag = ?," +
                    "mainHeader = ?, briefDef = ?, inputsCode = ?, companyName = ?, mySite = ?," +
                    "dopText = ?, action = ?, formCode = ?, statCode = ?, marketType = ?," +
                    "bgImg = ?, blackBox = ? where id = ?;";

    @Autowired
    private BasicDataSource ds;

    public void setDs(BasicDataSource ds) {
        this.ds = ds;
    }

    @Override
    public List<LeadSite> getAllLeadSites() {
        List<LeadSite> sites = new ArrayList<LeadSite>();
        Connection con = null;
        ResultSet res = null;
        try {
            con = ds.getConnection();
            Statement st = con.createStatement();
            res = st.executeQuery(SELECT_ALL_LEAD_SITES);
            sites = resultSetToLeadSitesList(res, sites);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                res.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return sites;
    }

    @Override
    public LeadSite findLeadSiteById(Integer id) {
        LeadSite leadSite = new LeadSite();
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_LEAD_SITE_BY_ID);
            psql.setInt(1, id);
            leadSite = resultSetToLeadSite(psql.executeQuery(), leadSite);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return leadSite;
    }

    @Override
    public List<LeadSite> getAllLeadSitesForUser(Integer userId) {
        List<LeadSite> sites = new ArrayList<LeadSite>();
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_ALL_LEAD_SITES_BY_USER_ID);
            psql.setInt(1, userId);
            sites = resultSetToLeadSitesList(psql.executeQuery(), sites);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return sites;
    }

    @Override
    public List<LeadSite> getAllActualLeadSitesForUser(Integer userId) {
        List<LeadSite> sites = new ArrayList<LeadSite>();
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_ALL_ACTUAL_LEAD_SITES_BY_USER_ID);
            psql.setInt(1, userId);
            sites = resultSetToLeadSitesList(psql.executeQuery(), sites);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return sites;
    }

    @Override
    public List<LeadSite> getAllLeadSitesForUserByStatus(Integer userId, String status) {
        List<LeadSite> sites = new ArrayList<LeadSite>();
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_ALL_LEAD_SITES_BY_USER_ID_AND_STATUS);
            psql.setInt(1, userId);
            psql.setString(2, status);
            sites = resultSetToLeadSitesList(psql.executeQuery(), sites);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return sites;
    }

    @Override
    public List<LeadSite> getAllSitesByStatus(String status) {
        List<LeadSite> sites = new ArrayList<LeadSite>();
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_ALL_LEAD_SITES_BY_STATUS);
            psql.setString(1, status);
            sites = resultSetToLeadSitesList(psql.executeQuery(), sites);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return sites;
    }

    @Override
    public void setStatusBySiteId(Integer siteId, String status) {
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SET_LEAD_SITE_STATUS_BY_ID);
            psql.setString(1, status);
            psql.setInt(2, siteId);
            psql.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    @Override
    public void addLeadSite(LeadSite leadSite) {
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(INSERT_LEAD_SITE);
            psql.setInt(1, leadSite.getOwnerId());
            psql.setInt(2, leadSite.getIdTemplate());
            psql.setString(3, leadSite.getTitleTag());
            psql.setString(4, leadSite.getKeywordTag());
            psql.setString(5, leadSite.getDescripTag());
            psql.setString(6, leadSite.getMainHeader());
            psql.setString(7, leadSite.getBriefDef());
            psql.setString(8, leadSite.getInputsCode());
            psql.setString(9, leadSite.getCompanyName());
            psql.setString(10, leadSite.getMySite());
            psql.setString(11, leadSite.getDopText());
            psql.setString(12, leadSite.getAction());
            psql.setString(13, leadSite.getFormCode());
            psql.setString(14, leadSite.getStatCode());
            psql.setString(15, leadSite.getMarketType());
            psql.setString(16, SiteStatuses.SITE_STATUS_ACTIVE); //при создании сайт активен
            psql.setString(17, leadSite.getBgImg());
            psql.setInt(18, leadSite.getBlackBox());
            psql.setDate(19, new Date(new java.util.Date().getTime())); //дата в момент создания сайта
            psql.setDate(20, new Date(new java.util.Date().getTime()));
            psql.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void deleteLeadSiteById(Integer leadSiteId) {
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con  = ds.getConnection();
            psql = con.prepareStatement(UPDATE_LEAD_SITE_TO_DELETED);
            psql.setInt(1, leadSiteId);
            psql.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void updateLeadSite(LeadSite leadSite) {
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(UPDATE_LEAD_SITE);
            psql.setInt(1, leadSite.getOwnerId());
            psql.setInt(2, leadSite.getIdTemplate());
            psql.setString(3, leadSite.getTitleTag());
            psql.setString(4, leadSite.getKeywordTag());
            psql.setString(5, leadSite.getDescripTag());
            psql.setString(6, leadSite.getMainHeader());
            psql.setString(7, leadSite.getBriefDef());
            psql.setString(8, leadSite.getInputsCode());
            psql.setString(9, leadSite.getCompanyName());
            psql.setString(10, leadSite.getMySite());
            psql.setString(11, leadSite.getDopText());
            psql.setString(12, leadSite.getAction());
            psql.setString(13, leadSite.getFormCode());
            psql.setString(14, leadSite.getStatCode());
            psql.setString(15, leadSite.getMarketType());
            psql.setString(16, leadSite.getBgImg());
            psql.setInt(17, leadSite.getBlackBox());
            psql.setInt(18, leadSite.getId());
            psql.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private LeadSite resultSetToLeadSite(ResultSet rs, LeadSite leadSite) throws SQLException {
        while (rs.next()) {
            leadSite = new LeadSite();
            leadSite.setId(rs.getInt("id"));
            leadSite.setIdTemplate(rs.getInt("idTemplate"));
            leadSite.setOwnerId(rs.getInt("ownerId"));
            leadSite.setTitleTag(rs.getString("titleTag"));
            leadSite.setKeywordTag(rs.getString("keywordTag"));
            leadSite.setDescripTag(rs.getString("descripTag"));
            leadSite.setMainHeader(rs.getString("mainHeader"));
            leadSite.setBriefDef(rs.getString("briefDef"));
            leadSite.setInputsCode(rs.getString("inputsCode"));
            leadSite.setCompanyName(rs.getString("companyName"));
            leadSite.setMySite(rs.getString("mySite"));
            leadSite.setDopText(rs.getString("dopText"));
            leadSite.setAction(rs.getString("action"));
            leadSite.setFormCode(rs.getString("formCode"));
            leadSite.setStatCode(rs.getString("statCode"));
            leadSite.setMarketType(rs.getString("marketType"));
            leadSite.setStatus(rs.getString("status"));
            leadSite.setBgImg(rs.getString("bgImg"));
            leadSite.setBlackBox(rs.getInt("blackBox"));
            leadSite.setDateMade(rs.getDate("dateMade"));
            leadSite.setDateLastChange(rs.getDate("dateLastChange"));
        }
        return leadSite;
    }

    private List<LeadSite> resultSetToLeadSitesList(ResultSet rs, List<LeadSite> sites) throws SQLException {
        while (rs.next()) {
            LeadSite leadSite = new LeadSite();
            leadSite.setId(rs.getInt("id"));
            leadSite.setOwnerId(rs.getInt("ownerId"));
            leadSite.setIdTemplate(rs.getInt("idTemplate"));
            leadSite.setTitleTag(rs.getString("titleTag"));
            leadSite.setKeywordTag(rs.getString("keywordTag"));
            leadSite.setDescripTag(rs.getString("descripTag"));
            leadSite.setMainHeader(rs.getString("mainHeader"));
            leadSite.setBriefDef(rs.getString("briefDef"));
            leadSite.setInputsCode(rs.getString("inputsCode"));
            leadSite.setCompanyName(rs.getString("companyName"));
            leadSite.setMySite(rs.getString("mySite"));
            leadSite.setDopText(rs.getString("dopText"));
            leadSite.setAction(rs.getString("action"));
            leadSite.setFormCode(rs.getString("formCode"));
            leadSite.setStatCode(rs.getString("statCode"));
            leadSite.setMarketType(rs.getString("marketType"));
            leadSite.setStatus(rs.getString("status"));
            leadSite.setBgImg(rs.getString("bgImg"));
            leadSite.setBlackBox(rs.getInt("blackBox"));
            leadSite.setDateMade(rs.getDate("dateMade"));
            leadSite.setDateLastChange(rs.getDate("dateLastChange"));

            sites.add(leadSite);
        }
        return sites;
    }
}
